package dexter.core.front;

import java.sql.*;

public class DatabaseManager {
    
	//CREATE TABLE USR (    mail varchar(25),    groupid varchar(25),   role varchar(25),   name varchar(25),   pwd varchar(40),   validated boolean default false,   PRIMARY KEY  (mail)  ) ENGINE=InnoDB;
	public static String USERS_TABLE = "uoc.USR";
	
	//CREATE TABLE EXPERIMENTS (    sequenceId integer,   time timestamp default CURRENT_TIMESTAMP,   
	//groupid varchar(25),   pathFiles varchar(100),   resultType boolean,    resultFase2 boolean 
	//default false,   resultFase2Info varchar(100) default NULL,   resultFase31 boolean default false,   
	//resultFase31Info varchar(100) default NULL,   resultFase32 boolean default false,   
	//resultFase32Info varchar(100) default NULL,   PRIMARY KEY  (sequenceId,groupid) ) ENGINE=InnoDB    
	public static String EXPERIMENTS_TABLE = "uoc.EXPERIMENTS";
    
	//CREATE TABLE JOBS (    jobid varchar(25),    groupid varchar(25),   publicip varchar(25),   PRIMARY KEY  (jobid)  ) ENGINE=InnoDB;
	public static String JOBS_TABLE = "uoc.JOBS";
    
    private static ThreadLocal<Connection> transactionConn = new ThreadLocal<Connection>();
	private static String dbUrl = "jdbc:mysql://localhost:3306/";
	private static String dbUser = "root";
	private static String dbPwd = "root";
    
    private static Connection init(String driver, String dbUrl, String dbUser, String dbPwd) {
		Connection conn = null;
		try {
			Class.forName (driver);
			System.out.println ("Connection to " + dbUrl);
			//DatabaseManager.conn = DriverManager.getConnection (dbUrl, dbUser, dbPassword);;
			DatabaseManager.dbUrl = dbUrl;
			DatabaseManager.dbUser = dbUser;
			DatabaseManager.dbPwd = dbPwd;
		} catch (ClassNotFoundException e) {
			System.err.println ("Driver not found : "+driver);
			e.printStackTrace();
		}
		return conn;
	}
    
    public static synchronized void beginTransaction() throws Exception {
        if ( transactionConn.get() != null ) {
            throw new Exception("This thread is already in a transaction");
        }
        Connection conn = getConnection();
        conn.setAutoCommit(false);
        transactionConn.set(conn);
    }
    
    public static void commitTransaction() throws Exception {
        if ( transactionConn.get() == null ) {
            throw new Exception("Can't commit: this thread isn't currently in a " +
                    "transaction");
        }
        transactionConn.get().commit();
        transactionConn.set(null);
    }
    
    public static void rollbackTransaction() throws Exception {
        if ( transactionConn.get() == null ) {
            throw new Exception("Can't rollback: this thread isn't currently in a " +
                    "transaction");
        }
        transactionConn.get().rollback();
        transactionConn.set(null);
    }

    public static Connection getConnection() throws Exception {
        if ( transactionConn.get() != null ) {
            return transactionConn.get();
        } else {
        	Connection conn = DriverManager.getConnection (dbUrl, dbUser, dbPwd);
            return conn;
        }
    }
    
    public static void releaseConnection(Connection conn) throws Exception {
         if ( transactionConn.get() == null ) {
            conn.close();
        }
    }
        
    public static void initDatabase(String driver, String dbUrl, String dbUser, String dbPassword) 
        throws Exception {
        
    	init(driver, dbUrl, dbUser, dbPassword);
    }

    
    private static int executeUpdate(String statement) 
            throws Exception {
        Connection conn = getConnection();
        try {
           PreparedStatement ps = conn.prepareStatement(statement);
           return ps.executeUpdate();
        } finally {
            releaseConnection(conn);
        }
    }

    private static ResultSet executeQueryNoParams(String statement) throws Exception {
    	Connection conn = getConnection();    	
    	PreparedStatement ps = conn.prepareStatement(statement);
       return ps.executeQuery();
    }
	
	public static boolean checkGroupById(String groupid) {
		try {		
			ResultSet rs = executeQueryNoParams("SELECT * FROM "
					+ USERS_TABLE + " WHERE groupid LIKE '"+groupid+"'");
			if (rs.next()) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
    public synchronized static String persistExperiment(String groupid, String pathFiles, boolean resultType, 
    		boolean resultFase2, String resultFase2Inf, 
    		boolean resultFase31, String resultFase31Inf, 
    		boolean  resultFase32, String resultFase32Inf ) {
    	
    	//fix '' and 100 char problems in info param
    	
    	String fase2Info = resultFase2Inf.replace("'", " ");
    	if (fase2Info.length()>98)
    		fase2Info = fase2Info.substring(0,98);
    	
    	String fase31Info = resultFase31Inf.replace("'", " ");
    	if (fase31Info.length()>98)
    		fase31Info = fase31Info.substring(0,98);
    	
    	String fase32Info = resultFase32Inf.replace("'", " ");
    	if (fase32Info.length()>98)
    		fase32Info = fase32Info.substring(0,98);
    	
    	String sequenceId = generateExperimentId();
    	java.sql.Timestamp  time = new java.sql.Timestamp(System.currentTimeMillis());
	
    	String sql = "insert into "+DatabaseManager.EXPERIMENTS_TABLE+" (sequenceId, time, groupid, pathFiles, resultType, resultFase2, resultFase2Info, resultFase31,  resultFase31Info, resultFase32,  resultFase32Info) VALUES ("+sequenceId+", '"+time+"', '"+groupid+"', '"+pathFiles+"', "+resultType+", "+resultFase2+", '"+fase2Info+"', "+resultFase31+", '"+fase31Info+"', "+resultFase32+", '"+fase32Info+"')";
		
    	try {
			DatabaseManager.executeUpdate(sql);
			return sequenceId;
		} catch (Exception e) {
			System.out.println("DATABASE ERROR : "+sql);
			e.printStackTrace();
		}
		return null;
		
	}
	
	private static String generateExperimentId(){
		try{
			ResultSet rs = executeQueryNoParams("SELECT * FROM "+ EXPERIMENTS_TABLE);
			if(rs!=null){
				int last=0;
				while(rs.next()){
					int actual=rs.getInt(1);
					if(last<actual)last=actual;
				}
				return Integer.toString(last+1);
			}
		}catch (Exception e){
			e.printStackTrace();
		}
		return null;
	}

	public static void persistJob(String jobId, String groupId,
			String publicIp) {
		
		String sql = "insert into "+DatabaseManager.JOBS_TABLE+" (jobid, groupid, publicip) VALUES ('"+jobId+"', '"+groupId+"', '"+publicIp+"')";
		
		try {
			DatabaseManager.executeUpdate(sql);
		} catch (Exception e) {
			System.out.println("DATABASE ERROR : "+sql);
			e.printStackTrace();
		}

	}
	
	//TEST ONLY
//	public static void main(String args[]) {
//		
//		 String correct = "101010101";
//		 String incorrect = "12123333";
//		 
//		 System.out.println("checking ("+correct+") : "+checkGroupById(correct));
//		 System.out.println("checking ("+incorrect+") : "+checkGroupById(incorrect));
//		 
//		 persistExperiment(correct, ".", true, 
//		    		true, "Successful", 
//		    		true,"Successful", 
//		    		false, "Syntax Error");
//		 
//		 persistExperiment(correct, ".", false, 
//		    		false, "Error", 
//		    		true,"Successful", 
//		    		true, "Syntax Error");
//	}
	

}
